USE [ServerMonitor]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AllServerSQLAVGPerfCount]'))
DROP VIEW [dbo].[AllServerSQLAVGPerfCount]
GO
USE [ServerMonitor]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[AllServerSQLAVGPerfCount]
AS
  SELECT 
  [ServerId],
  CONVERT(DATETIME,CONVERT(VARCHAR(13),[LogTime],120) + ':00') AS LogHour,
  AVG([ConnectCount]) AS AVGConnect,
  MIN([ConnectCount]) AS MINConnect,
  MAX([ConnectCount]) AS MAXConnect,
  AVG([BlockedCount]) AS AVGBlocked,
  MIN([BlockedCount]) AS MINBlocked,
  MAX([BlockedCount]) AS MAXBlocked,
  AVG([Deadlocks]) AS AVGDeadLocks,
  MIN([Deadlocks]) AS MINDeadLocks,
  MAX([Deadlocks]) AS MAXDeadLocks,
  AVG([RequestsPerSec]) AS AVGRequestsBat,
  AVG([CompilationsPerSec]) AS AVGComp_Bat,
  AVG([ReCompilationsPerSec]) AS AVGReComp_Bat,
  AVG([BufferHitRatio]) AS AVGBufferHitRatio,
  AVG([PageLife]) AS AVGPageLife,
  AVG([LockRequestsPerSec]) AS AVGLockRequests,
  AVG([LockTimeoutsPerSec]) AS AVGLockTimeout,
  AVG([locksWaitsPerSec]) AS AVGLocksWaits,
  AVG([ActiveTran]) AS AVGActiveTran
  FROM [ServerMonitor].[dbo].[AllServerSQLPerformanceCount]
  GROUP BY [ServerId],CONVERT(DATETIME,CONVERT(VARCHAR(13),[LogTime],120) + ':00')

GO


